package persistence.client.clientDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import client.exceptions.BadClientException;
import client.exceptions.MissingClientException;
import dbExceptions.DataBaseErrorException;
import persistence.DAO.DAOHelper;
import business.client.entities.Client;

public class ClientDAO {
	
	private static ClientDAO instance = null;
	private ClientDAO()	{}
	public static ClientDAO getInstance(){
		if(instance==null)
			instance = new ClientDAO();
		return instance;
	}
	
	public void addClient(Client client) throws MissingClientException, DataBaseErrorException, BadClientException{
		if (client==null) throw new MissingClientException(null);
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			String sql="INSERT INTO Client (name,lastName,address,tel,cel,birthdate,registrationDate,active,CI) VALUES ('";
			sql=sql+ client.getName()+"','";
			sql=sql+ client.getLastName()+"','";
			sql=sql+ client.getAddress()+"','";
			sql=sql+ client.getTel()+"','";
			sql=sql+ client.getCel()+"','";
			sql=sql+ client.getBirthdate()+"','";
			sql=sql+ new java.sql.Date(client.getRegistrationDate().getTime())+"',";
			sql=sql+"1,"+client.getClientId()+ ")";
			st.execute(sql);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
		}
	}
	
	public void updateClient(Client client) throws MissingClientException, BadClientException, DataBaseErrorException{
		if (client==null) throw new MissingClientException(null);
		if (!clientExists(client)){
			throw new BadClientException(null);
		}
			
		String sql = "UPDATE Client SET ";
		sql = sql + "name='" + client.getName() + "'";
		
		sql = sql + ", address='" + client.getAddress()+"'";
		
		sql = sql + ", cel='" + client.getCel()+"'";
		
		sql = sql + ", tel='" + client.getTel()+"'";
		
		sql = sql + ", birthdate='" + client.getBirthdate()+"'";
		
		sql = sql + ", lastName='" + client.getLastName()+"'";

		sql = sql + " WHERE inner_key="
				+ String.valueOf(client.getClientId());
		Connection con=null;
		try {
		con=DAOHelper.getInstance().getConnection();
		Statement st=con.createStatement();
			st.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
		}
	}
	
	public void deleteClient(Client client) throws MissingClientException, DataBaseErrorException, BadClientException{
		if (client==null) throw new MissingClientException(null);
		if (!clientExists(client)){
			throw new BadClientException(null);
		}
		
			String sql = "UPDATE Client SET ";
			sql = sql + " active='0'";
			sql = sql + " WHERE CI="
					+ String.valueOf(client.getClientId());
			Connection con=null;
			try {
				con=DAOHelper.getInstance().getConnection();
				Statement st=con.createStatement();
				st.executeUpdate(sql);

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				if(con!=null)
					try {
						con.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}	
			}

	}
	
	public Client[] getClients(){
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery(
				  "SELECT CI,name,lastName,address,tel,cel,registrationDate,birthdate FROM Client WHERE active=1 AND inner_key>0");
				  ArrayList<Client> clientes=new ArrayList<Client>();
				  
				  while(r.next()){
					  
					  Client c=new Client(r.getInt(1), r.getString(2), r.getString(3), r.getString(4), r.getString(5), r.getString(6), new Date(r.getLong(7)), r.getString(8));
					  clientes.add(c);
					  }
				  
				  Client[] ingr=new Client[clientes.size()];
				  clientes.toArray(ingr); return ingr; } catch (SQLException e) { e.printStackTrace();
				  }finally{
						if(con!=null)
							try {
								con.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}	
					}
		
				  return null;
	}
	
	public Client getClient(int ci){
		Connection con=null;
		
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st= con.createStatement();
			ResultSet r=st.executeQuery(
				  "SELECT CI,name,lastName,address,tel,cel,registrationDate,birthdate FROM Client WHERE active=1 AND inner_key="+String.valueOf(ci));
				 
				  r.next();
				  Client ingr=new Client(r.getInt(1), r.getString(2), r.getString(3), r.getString(4), r.getString(5), r.getString(6), new Date(r.getLong(7)), r.getString(8));
				  return ingr;
				  } catch (SQLException e) { e.printStackTrace();
				  }finally{
						if(con!=null)
							try {
								con.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}	
					}
				  return null;
	}

	private boolean clientExists(Client c) throws DataBaseErrorException{
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery("SELECT COUNT(CI) FROM Client WHERE inner_key="+String.valueOf(c.getClientId()));
			r.next();
			int a=r.getInt(1);
			if (a==0) return false;
			if (a==1) return true;
			if (a>1) throw new DataBaseErrorException();  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
		}
		return false;
		
	}
	
}
